﻿
CREATE PROCEDURE [dbo].[InchidereConturiVenituriCheltuieli] 
	-- Add the parameters for the stored procedure here
	@DataDoc DATETIME,
	@DeLaData DATETIME,
	@PanaLaData DATETIME,
	@CodUnitate smallint
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	DECLARE @NrDocAdd int
	DECLARE @LastNrDoc bigint
	SET NOCOUNT ON;
    BEGIN TRAN
    
    DECLARE my_cursor CURSOR FOR 
	SELECT
	ValoareLei,
	0 AS ValoareValuta,
	NumarDoc NumarDoc,
	@DataDoc AS DataDoc,
	'IC' AS NumarNota,
	2 AS TipFormula,
	IdJurnal,
	Cont,
	CASE WHEN Cont = '6' THEN 'Inchidere cont de cheltuieli' WHEN Cont='7' THEN 'Inchidere cont de venituri' ELSE '' END AS Observatii
	FROM (SELECT 
		ABS(SUM(soldd)) ValoareLei,
		Cont,IDJurnal,(SELECT Max(NumarDoc) FROM [RulajeDoc] WHERE [CodUnitate]=@CodUnitate) NumarDoc
		FROM
			(SELECT SUM(a.ValoareLei) soldd, dbo.GetContClass(ContDebit) Cont,IDJurnal FROM Rulaje a
			JOIN RulajeDoc b ON a.IdDocument=b.IdDocument 
			WHERE b.DataDoc>=@DeLaData AND b.DataDoc <=@PanaLaData AND a.[CodUnitate] = @CodUnitate
			GROUP BY dbo.GetContClass(ContDebit),IDJurnal
			UNION
			SELECT -SUM(a.ValoareLei) soldd, dbo.GetContClass(ContCredit) Cont,IDJurnal FROM Rulaje a
			JOIN RulajeDoc b ON a.IdDocument=b.IdDocument 
			WHERE b.DataDoc>=@DeLaData AND b.DataDoc <=@PanaLaData  AND a.[CodUnitate] = @CodUnitate
			GROUP BY dbo.GetContClass(ContCredit),IDJurnal) t1
		WHERE Cont>='6' AND Cont<='7' AND soldd<>0
		GROUP BY Cont,IDJurnal) t2
	WHERE ValoareLei<>0

	OPEN my_cursor;
	
	DECLARE @valoarelei decimal(20,2),@valoarevaluta decimal(20,2),@nrdoc decimal(18,0)
	DECLARE @numarNota varchar(50),@observatii varchar(200),@clasacont varchar(1)
	DECLARE @idjurnal int
	DECLARE @tipformula smallint
	DECLARE @datadoc1 datetime 
	

	FETCH NEXT FROM my_cursor 
	INTO @valoarelei,@valoarevaluta,@nrdoc,@DataDoc1,@numarNota,@tipformula,@idjurnal,@clasacont,@observatii;

	WHILE @@FETCH_STATUS = 0
	BEGIN


	INSERT INTO [RulajeDoc]
	([ValoareLei],[ValoareValuta],[NumarDoc],[DataDoc],[NumarNota],[TipFormula],[IDJurnal]
	,[Observatii],[xUser],[xHost],[CodUnitate])   
	VALUES (@valoarelei,@valoarevaluta,@nrdoc,@DataDoc1,@numarNota,@tipformula,@idjurnal,@observatii,
	system_user,host_name(),@CodUnitate)
	

    PRINT(SCOPE_IDENTITY())
	PRINT(@NrDocAdd)
	PRINT(@DeLaData)
	PRINT(@PanaLaData)
	
    --PRINT(@LastNrDoc)
	IF @@ERROR <>0  GOTO EroareDoc
		INSERT INTO [Rulaje]
		([ContDebit],[ContCredit],[CodValuta],[IDDocument] ,[ValoareValuta],[Curs],[ValoareLei],[DataDocJust]
			,[TipDocJust] ,[NumarDocJust] ,[ExplicatiiDocJust] ,[NrCrt],[CodUnitate])
			SELECT 
			CASE WHEN SCont = '7' THEN Cont WHEN SCont='6' THEN t3.ContDebit ELSE 'N/A' END ContDebit,
			CASE WHEN SCont = '7' THEN t3.ContCredit WHEN SCont='6' THEN Cont ELSE 'N/A' END ContCredit,
			0 AS CodValuta,
			SCOPE_IDENTITY() AS IDDocument,
			0 AS ValoareValuta,
			0 AS Curs,
			ValoareLei,
			@DataDoc AS DataDocJust,
			'IC' AS TipDocJust,
			0 AS NumarDocJust,
			'Inchidere Conturi'  AS ExplicatiiDocJust,
			0 AS NrCrt,
			@CodUnitate AS CodUnitate
			FROM (SELECT 
					CASE WHEN SCont='6' THEN SUM(soldd) ELSE -SUM(soldd) END ValoareLei,SCont,[CodUnitate],
					Cont,IDJurnal, @@IDENTITY AS NumarDoc,
					0 RowNumber FROM
						(SELECT SUM(a.ValoareLei) soldd, ContDebit Cont,dbo.GetContClass(ContDebit) SCont,IDJurnal,a.[CodUnitate] FROM Rulaje a
						JOIN RulajeDoc b ON a.IdDocument=b.IdDocument 
						WHERE b.DataDoc>=@DeLaData AND b.DataDoc <=@PanaLaData AND a.[CodUnitate]=@CodUnitate AND b.IDJurnal=@idjurnal
						GROUP BY ContDebit,dbo.GetContClass(ContDebit),IDJurnal,a.[CodUnitate]
						UNION
						SELECT -SUM(a.ValoareLei) soldd, ContCredit Cont,dbo.GetContClass(ContCredit) SCont,IDJurnal,a.[CodUnitate] FROM Rulaje a
						JOIN RulajeDoc b ON a.IdDocument=b.IdDocument 
						WHERE b.DataDoc>=@DeLaData AND b.DataDoc <=@PanaLaData  AND a.[CodUnitate]=@CodUnitate  AND b.IDJurnal=@idjurnal
						GROUP BY ContCredit,dbo.GetContClass(ContCredit),IDJurnal,a.[CodUnitate]) t1
					WHERE dbo.GetContClass(Cont)= @clasacont AND soldd<>0
					GROUP BY Cont,SCont,IDJurnal,[CodUnitate]) t2 
                    JOIN ConturiCoresp t3 
                    ON ((t3.ContDebit = t2.Cont) OR (t3.ContCredit = t2.Cont)) AND t3.IDJurnal = t2.IdJurnal AND t3.CodUnitate=t2.CodUnitate
			   	  					
		WHERE ValoareLei<>0 
		IF @@ERROR <>0  GOTO EroareRul
		
		FETCH NEXT FROM my_cursor 
			INTO @valoarelei,@valoarevaluta,@nrdoc,@DataDoc1,@numarNota,@tipformula,@idjurnal,@clasacont,@observatii;
	END
	CLOSE my_cursor;
	DEALLOCATE my_cursor;		
		
		
		COMMIT TRAN
	GOTO Gata
	EroareDoc:
		ROLLBACK
		RAISERROR('Eroare la generarea documentului ',16,-1)
		return
	EroareRul:
		ROLLBACK
		RAISERROR('Eroare la generarea rulajului ',16,-1)
		return

	Gata:
END